package job;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SubjectDao {

    public int add(Subject s) {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        try {
            String sql = "INSERT INTO s_subject(subjectname,classhour,gradeid) VALUES(?,?,?);";
            ps = connection.prepareStatement(sql);
            ps.setString(1, s.getSubjectname());
            ps.setInt(2, s.getClasshour());
            ps.setLong(3, s.getGradeid());
            int num = ps.executeUpdate();
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
        }
        return 0;
    }

    public int update(Subject s) {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        try {
            String sql = "update s_subject set subjectname=?,classhour=?,gradeid=?  where id=?";
            ps = connection.prepareStatement(sql);
            ps.setString(1, s.getSubjectname());
            ps.setInt(2, s.getClasshour());
            ps.setLong(3, s.getGradeid());
            int num = ps.executeUpdate();
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
        }
        return 0;
    }

    public int del(long id) {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        try {
            String sql = "delete from s_subject  where id=?";
            ps = connection.prepareStatement(sql);
            ps.setLong(1, id);
            int num = ps.executeUpdate();
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
        }
        return 0;
    }

    public List<Subject> query() {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Subject> list = new ArrayList();
        try {
            ps = connection.prepareStatement("select * from s_subject order by id desc");
            rs = ps.executeQuery();
            while (rs.next()) {
                Subject su = new Subject();
                su.setId(rs.getLong("id"));
                su.setSubjectname(rs.getString("subjectname"));
                su.setClasshour(rs.getInt("classhour"));
                su.setGradeid(rs.getLong("gradeid"));
                list.add(su);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, connection);
        }
        return null;

    }
}
